using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Windows.Forms;
using abc_ns;
namespace ece750project
{
	public static  class DB_BusinessRules
	{
        public static DataTable Get_person(string Person_ID)
        {
            string SQL = "Select * From person Where person_id=" + Person_ID;
            return DBUtility.Get_Data(SQL);
        }

        public static bool Grant_doc_permission(int doc_id, int patient_id, int vist_id, int by_doc)
        {
            return Convert.ToBoolean(DBUtility.call_func("add_doc_permission", doc_id.ToString(), patient_id.ToString(), vist_id.ToString(), by_doc.ToString()));
        }

        public static DataTable Get_visit(int visit_id)
        {
            return DBUtility.call_sp("get_visit", visit_id.ToString());
        }

        public static DataTable Get_visit_Notes(int visit_id)
        {
            return DBUtility.call_sp("get_visit_notes", visit_id.ToString());
        }
        public static DataTable Get_visit_prescriptions(int visit_id)
        {
            return DBUtility.call_sp("get_visit_prescriptions", visit_id.ToString());
        }

        public static DataTable Get_Health_indecations()
        {
            return DBUtility.call_sp("get_health_indecations");
        }

        public static DataTable get_patient_schedule(int patient_id_var)
        {
            return DBUtility.call_sp("get_patient_schedule", patient_id_var.ToString());
        }

        public static DataTable get_supervisor(int staff_id)
        {
            return DBUtility.call_sp("get_supervisor", staff_id.ToString());
        }


        public static DataTable get_doc_staff(int doc_id_var)
        {
            return DBUtility.call_sp("get_doc_staff", doc_id_var.ToString());
        }

        public static DataTable finance_prescriptions(DateTime sdate_var, DateTime edate_var)
        {
            return DBUtility.call_sp("finance_prescriptions", sdate_var.ToShortDateString(), edate_var.ToShortDateString());
        }

        public static string get_person_name(int person_id_var)
        {
            return DBUtility.call_func("get_person_name", person_id_var.ToString()).ToString();
        }

        public static void grant_staff(int staff_id, bool granted)
        {
            DBUtility.call_func("grant_staff", staff_id.ToString(), granted.ToString());
        }
        public static string  get_supervisor_Name(int staff_id)
        {
            DataTable DT=get_supervisor(staff_id);
            if (DT != null && DT.Rows.Count > 0)
            {
                string Name = DT.Rows[0]["supervisor_name"].ToString();
                return Name;
            }
            return "";
        }

        public static int get_supervisor_ID(int staff_id)
        {
            DataTable DT = get_supervisor(staff_id);
            if (DT != null && DT.Rows.Count > 0)
            {
                return Convert.ToInt32(DT.Rows[0]["supervisor_id"]);
                
            }
            return 0;
        }

        public static DataTable serch_patient(string last_name_var, string phone_number_var)
        {
            if (last_name_var != "")
                last_name_var = "'%" + last_name_var + "%'";
            else
                last_name_var = "'#####'";

            if (phone_number_var != "")
                phone_number_var = "'%" + phone_number_var + "%'";
            else
                phone_number_var = "'#####'";

            
            return DBUtility.call_sp("serch_patient", last_name_var, phone_number_var);
        }

        public static DataTable get_person(int person_id_var)
        {
            return DBUtility.call_sp("get_person", person_id_var.ToString());
        }

        public static bool Revok_doc_permission(int doc_id, int patient_id, int vist_id, int by_doc)
        {
            return Convert.ToBoolean(DBUtility.call_func("delete_doc_permission", doc_id.ToString(), patient_id.ToString(), vist_id.ToString(), by_doc.ToString()));
        }
        public static bool add_prescription(int visit_id, string prescription, string discription, DateTime valid_date)
        {

            bool functionReturnValue = false;
            string SQL = null;
            SQL = "add_prescription";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("visit_id_var", visit_id);
            cmd.Parameters.AddWithValue("perscription_var", prescription);
            cmd.Parameters.AddWithValue("description_var", discription);
            cmd.Parameters.AddWithValue("valid_untill_var", valid_date);

            functionReturnValue = DBUtility.ExcuteSQL(SQL, cmd);
            cmd.Dispose();
            cmd = null;
            return functionReturnValue;

        }

        public static bool Add_schedule_entry(int doc_id_var, int service_id_var, DateTime  service_date_var, DateTime service_time_var, int Length_var)
        {

            bool functionReturnValue = false;
            string SQL = null;
            SQL = "Add_schedule_entry";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("doc_id_var", doc_id_var);
            cmd.Parameters.AddWithValue("service_id_var", service_id_var);
            cmd.Parameters.AddWithValue("service_date_var", service_date_var);
            cmd.Parameters.AddWithValue("service_time_var", service_time_var);
            cmd.Parameters.AddWithValue("Length_var", Length_var);

            functionReturnValue = DBUtility.ExcuteSQL(SQL, cmd);
            cmd.Dispose();
            cmd = null;
            return functionReturnValue;

        }



        public static void add_visit_notes(int visit_id, string note)
        {

            DBUtility.call_func("add_visit_note", visit_id.ToString(), "'" + note + "'");
        }

        public static void add_visit(int schedule_id_var, int visit_type_var, string reason_var, string diagnosis_var, int health_indic_var)
        {

            DBUtility.call_func("add_visit", schedule_id_var.ToString(), visit_type_var.ToString(), "'" + reason_var + "'", "'" + diagnosis_var + "'", health_indic_var.ToString());
        }

        public  static bool Add_Patient(ref abc_class.person_Struct pData)
        {
            bool functionReturnValue = false;
            string SQL = null;
            SQL = "insert_patient";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("first_name_var", pData.first_name);
            cmd.Parameters.AddWithValue("last_name_var", pData.last_name);
            cmd.Parameters.AddWithValue("person_type_var", pData.person_type);
            cmd.Parameters.AddWithValue("phone_number_var", pData.phone_number);
            cmd.Parameters.AddWithValue("phone_emergency_var", pData.phone_emergency);
            cmd.Parameters.AddWithValue("street_no_var", pData.street_no);
            cmd.Parameters.AddWithValue("street_name_var", pData.street_name);
            cmd.Parameters.AddWithValue("city_var", pData.city);
            cmd.Parameters.AddWithValue("province_var", pData.province);
            cmd.Parameters.AddWithValue("country_var", pData.country);
            cmd.Parameters.AddWithValue("postal_code_var", pData.postal_code);
            cmd.Parameters.AddWithValue("ssn_var", pData.ssn);
            cmd.Parameters.AddWithValue("sex_var", pData.sex);
            cmd.Parameters.AddWithValue("dob_var", pData.dob);
            cmd.Parameters.AddWithValue("email_var", pData.email);
            cmd.Parameters.AddWithValue("default_doctor_var", pData.default_doctor);
            cmd.Parameters.AddWithValue("health_no_var", pData.health_no);
            cmd.Parameters.AddWithValue("insurance_no_var", pData.insurance_no);

            functionReturnValue =DBUtility.ExcuteSQL(SQL, cmd);
            cmd.Dispose();
            cmd = null;
            return functionReturnValue;
        }

        public static DataTable  get_doc_schedule(int doc_id_var, DateTime date_var)
        {
            DataTable functionReturnValue;
            string SQL = null;
            SQL = "get_doc_schedule";
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("doc_id_var", doc_id_var);
            cmd.Parameters.AddWithValue("date_var", date_var);

            functionReturnValue = DBUtility.Get_Data(SQL, cmd);
            cmd.Dispose();
            cmd = null;
            return functionReturnValue;

        }

        public static DataTable Get_Doc_Patients(int Doc_ID)
        {

            return DBUtility.call_sp("get_doc_patients", Doc_ID.ToString());
        }

        public static bool Is_Doc_permited_to_vist(int Doc_ID,int Patient_ID, int visit_ID)
        {
          return  Convert.ToBoolean(DBUtility.call_func("is_doc_permited_to_visit", Doc_ID.ToString(),Patient_ID.ToString(),visit_ID.ToString()));
        }



        public static DataTable get_doctor_revenue()
        {
            return DBUtility.call_sp("get_doctor_revenue");
        }

        public static DataTable get_Active_doc_list()
        {
            return DBUtility.call_sp("get_Active_doc_list");
        }

        public static DataTable  Get_docs_list()
        {

            return DBUtility.call_sp("get_doc_list");
        }


        public static DataTable get_patient_recent_appointments(int patient_id_var, int doctor_id_var)
        {

            return DBUtility.call_sp("get_patient_recent_appointments", patient_id_var.ToString(), doctor_id_var.ToString());
        }

        public static DataTable Get_Patient_visits(int Patient_ID,int Doc_ID)
        {

            return DBUtility.call_sp("get_patient_visit",Patient_ID.ToString(), Doc_ID.ToString());
        }
		
        public static DataTable get_patient_visit_notOriginal(int Patient_ID,int Doc_ID)
        {

            return DBUtility.call_sp("get_patient_visit_notOriginal",Patient_ID.ToString(), Doc_ID.ToString());
        }
		
		

        public static DataTable get_Defualt_docs()
        {
            return DBUtility.call_sp("get_dafualt_docs");
        }

        public static DataTable get_medical_dep_list()
        {
            return DBUtility.call_sp("get_medical_dep_list");
        }

        public static DataTable get_dep__services_list(int dep_id_var)
        {
            return DBUtility.call_sp("get_dep_services_list", dep_id_var.ToString());
        }
        public static DataTable get_docs_for_service(int service_id_var)
        {
            return DBUtility.call_sp("get_docs_for_service", service_id_var.ToString());
        }

        public static DataTable get_doc_service_schedule(int serv_id, int doc_id)
        {
            return DBUtility.call_sp("get_doc_service_schedule", serv_id.ToString(), doc_id.ToString());
        }

        public static DataTable get_doc_services_list(int doc_id_var)
        {
            return DBUtility.call_sp("get_doc_services_list", doc_id_var.ToString());
        }


        public static void book_appointment(int schedule_id_var, int patient_id_var, int staff_id_var)
        {
            DBUtility.call_func("book_appointment", schedule_id_var.ToString(), patient_id_var.ToString(), staff_id_var.ToString());
        }

        public static void cancel_appointment(int staff_id_var, int schedule_id_var)
        {
            DBUtility.call_func("cancel_appointment", staff_id_var.ToString(), schedule_id_var.ToString());
        }

        public static void Confirm_appointment(int staff_id_var, int schedule_id_var)
        {
            DBUtility.call_func("confirm_appointment", staff_id_var.ToString(), schedule_id_var.ToString());
        }

           
    }
} 


